﻿	DECLARE 
		@LANGUAGE_KEY VARCHAR(15)

	SELECT	@LANGUAGE_KEY = [MALO_IVBABY].dbo.FC_GET_LANGUAGE(@pEHUSER)

    -- Insert statements for procedure here
    
	SELECT A.DOCTOR_ORDER_KEY,A.STATUS_KEY,A.APPLY_USER_KEY,U.[USER_NAME]
    INTO #TMP_STATUS
	FROM TB_DOCTOR_ORDER_STATUS_LOG A
	JOIN 
	(SELECT DOCTOR_ORDER_KEY,MAX(DOCTOR_ORDER_LOG_KEY) AS DOCTOR_ORDER_LOG_KEY FROM TB_DOCTOR_ORDER_STATUS_LOG
	WHERE STATUS_KEY=10090
	GROUP BY DOCTOR_ORDER_KEY) B ON A.DOCTOR_ORDER_LOG_KEY= B.DOCTOR_ORDER_LOG_KEY
	JOIN TB_IVBABY_USER U ON U.USER_KEY= A.APPLY_USER_KEY

    SELECT A.*
    ,CONVERT(VARCHAR(16),A.CREATE_DATE,120) as DOCTOR_ORDER_CREATE_DATE 
    ,ISNULL(S.[USER_NAME],'') AS USER_10090_KEY_DESC
   INTO #TMP
   FROM FN_DOCTOR_ORDER_DETAIL(@LANGUAGE_KEY,'N') A
   LEFT JOIN #TMP_STATUS S ON A.DOCTOR_ORDER_KEY= S.DOCTOR_ORDER_KEY
    WHERE  1=1
	      { AND A.VISIT_KEY IN (@@pVISIT_KEY_STRING) }
		  {AND ITEM_NAME LIKE '%'+ ISNULL(@pDOCTOR_ORDER_KEY_DESC,'') +'%'}
          {AND A.DOCTOR_ORDER_TYPE= @pDOCTOR_ORDER_TYPE }
		  {AND A.VISIT_KEY =@pVISIT_KEY}
          
    UPDATE #TMP
    SET  STATUS_KEY_DESC = STATUS_KEY_DESC + '/' + ST.STATUS_NAME 
    FROM GLOBAL..TB_STATUS ST       
    WHERE #TMP.DOCTOR_ORDER_TYPE IN ('DRUG','BLOOD')
        AND #TMP.STATUS_KEY=10095 
        AND  #TMP.REAL_STATUS_KEY= ST.STATUS_KEY AND ST.LANGUAGE_KEY=@LANGUAGE_KEY   
    
    
  SELECT * FROM #TMP 